import sys

import pymysql

from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *


class modify_window(QDialog):
    Signal_TwoParameter = pyqtSignal(str)

    def __init__(self, parent=None):
        super(modify_window, self).__init__(parent)
        self.setWindowTitle('修改数据')

        # 在布局中添加部件
        layout = QVBoxLayout(self)
        self.resize(640, 200)

        self.label = QLabel(self)
        self.label.setText('请输入需要修改的数据（车牌号为必填项！）')

        self.tablewidget1 = QTableWidget()
        self.tablewidget1.setRowCount(1)
        self.tablewidget1.setColumnCount(6)
        # 设置列名
        self.tablewidget1.setHorizontalHeaderLabels(["车牌号", "时间", "姓名", "手机号", "是否处理", "图片"])

        button_clean = QPushButton("清空数据")
        button_clean.clicked.connect(self.clean)

        hbox = QHBoxLayout()
        hbox.addStretch(1)
        hbox.addWidget(button_clean)
        hbox.addStretch(1)

        layout.addWidget(self.label)
        layout.addWidget(self.tablewidget1)
        # 使用两个button(ok和cancel)分别连接accept()和reject()槽函数
        buttons = QDialogButtonBox(
            QDialogButtonBox.Ok | QDialogButtonBox.Cancel,
            Qt.Horizontal, self)
        buttons.accepted.connect(self.modify_handle)
        buttons.rejected.connect(self.reject)

        layout.addLayout(hbox)
        layout.addWidget(buttons)
    def modify_handle(self):

        # 获取表格内容
        id = self.tablewidget1.item(0, 0)
        time = self.tablewidget1.item(0, 1)
        name = self.tablewidget1.item(0, 2)
        phone = self.tablewidget1.item(0, 3)
        deal = self.tablewidget1.item(0, 4)
        image = self.tablewidget1.item(0, 5)

        dict = {'Mid':id, 'Mdate':time, 'Mname':name, 'Mphone':phone, 'Mdeal':deal, 'Mimage':image}
        dict_not_None = {}
        dict_not_NULL = {}

        string = ''

        if id == None:
            QMessageBox.critical(self, '错误', '车牌号为必填项！', QMessageBox.Yes)
            return

        else:
            for i in dict:
                if dict[i] != None:
                    dict[i] = dict[i].text()
                    dict_not_None[i]=dict[i]
            print("非None", dict_not_None)
            for j in dict_not_None:
                if dict_not_None[j] != '':
                    dict_not_NULL[j]=dict_not_None[j]
            print("非空", dict_not_NULL)
            if('Mid' not in dict_not_NULL.keys()):
                QMessageBox.critical(self, '错误', '车牌号为必填项！', QMessageBox.Yes)

            # 创建条件语句
            for i in dict_not_NULL.items():
                Col_Name = i[0]
                Value = "'" + i[1] + "'"
                statement = Col_Name + '=' + Value + ','
                string = string + statement
            print(string)

            Mid = 'Mid ' + '=' + "'" + dict_not_NULL['Mid'] + "'"
            string_final = 'SET ' + string[0:-1]

            reply = QMessageBox.warning(self, '警告', '修改后无法恢复！', QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
            if reply == QMessageBox.Yes:
                try:
                    # 删除的sql语句
                    sql = "UPDATE tab " + string_final + " where " + Mid
                    print(sql)

                    conn = pymysql.connect(host='localhost', port=3306, user='root', password="root", db="tab")
                    cur = conn.cursor()
                    # 执行sql语句，并发送给数据库
                    cur.execute(sql)
                    conn.commit()

                    cur.close()
                    conn.close()

                    self.Signal_TwoParameter.emit("modify_window_succeed")
                    print("修改成功")
                except Exception as e:
                    print(e)
                    QMessageBox.critical(self, '错误', '数据库中有相同的车牌号与时间，请更改数据！！', QMessageBox.Yes)
                    self.Signal_TwoParameter.emit("modify_window_fail")

    def clean(self):
        self.tablewidget1.clearContents()

if __name__ == '__main__':
    app = QApplication(sys.argv)
    main = modify_window()
    main.show()
    sys.exit(app.exec_())
